package cn.com.elite.workorder;

import org.springframework.jdbc.datasource.*;
import javax.sql.*;
import java.util.*;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import cn.com.elite.util.*;
import javax.servlet.http.HttpSession;



import java.sql.*;

import javax.sql.DataSource;
import javax.naming.*;


public class WorkOrderBean {
	public WorkOrderBean() {
	}


    private DataSource dataSource;
    private DataSourceTransactionManager tran;

    public DataSource getDataSource() {
      return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
    }

    public void setTran(DataSourceTransactionManager tran) {
      this.tran = tran;
    }

    public DataSourceTransactionManager getTran() {
      return tran;
    }

    public int SMSStatus(String msgid, String mobile
    		, String subsn, String status) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = WORKORDERFRAME.SMSStatusSQL;
            sql = sql.replaceAll("@@msgid", msgid);
            sql = sql.replaceAll("@@mobile", mobile);
            sql = sql.replaceAll("@@subsn", subsn);
            sql = sql.replaceAll("@@status", status);

        int resultList = 0;
        System.out.println("sql:"+sql);
        try{
          //jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.update(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }
    
    public int TS_QueryUndwrtStatus(String ProposalNo, String ProposalStatus, String Underwriteopinion, String UnderwriteDate) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = WORKORDERFRAME.TS_QueryUndwrtStatusSQL;
            sql = sql.replaceAll("@@ProposalNo", ProposalNo);
            sql = sql.replaceAll("@@ProposalStatus", ProposalStatus);
            sql = sql.replaceAll("@@Underwriteopinion", Underwriteopinion);
            sql = sql.replaceAll("@@UnderwriteDate", UnderwriteDate);

        int resultList = 0;
        System.out.println("sql:"+sql);
        try{
          //jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.update(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }
    
    public int ReceiveSMS(String mobile, String message
    		, String gw, String spnumber, String cell) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = WORKORDERFRAME.ReceiveSMSSQL;
            sql = sql.replaceAll("@@mobile", mobile);
            sql = sql.replaceAll("@@message", message);
            sql = sql.replaceAll("@@gw", gw);
            sql = sql.replaceAll("@@spnumber", spnumber);
            sql = sql.replaceAll("@@cell", cell);

        int resultList = 0;
        System.out.println("sql:"+sql);
        try{
          //jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.update(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }
    
    public int saveSMS(String msgid, String mobile, String status) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = WORKORDERFRAME.ReceiveSMSSQL;
            sql = sql.replaceAll("@@msgid", msgid);
            sql = sql.replaceAll("@@mobile", mobile);
            sql = sql.replaceAll("@@status", status);

        int resultList = 0;
        System.out.println("sql:"+sql);
        try{
          //jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.update(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }
    
    public int updateDocumentout(String msgid, String status) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "update documentout set Status="+status+",TrialCount=TrialCount+1 where DocumentOut_GUID='"+msgid+"'";

        int resultList = 0;
        System.out.println("sql:"+sql);
        try{
          //jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.update(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }
    
    public int updateDocumentoutFail(String msgid, String status) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "update documentout set Status="+status+" ,TrialCount=TrialCount+1 where DocumentOut_GUID='"+msgid+"'";

        int resultList = 0;
        System.out.println("sql:"+sql);
        try{
          //jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.update(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }
    
    
    public List getSonNode(String workorderframe_id, WorkOrderActionForm form) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

	  String sql = "select * from two_node a, two_link b \n" +
		  " where b.workorderframe_id='" + workorderframe_id + "' and  \n" +
		  " b.sonnode_id=a.workordernode_id and b.fathernode_id='" + form.getCurStep_ID() + "'";

	  List resultList = null;
System.out.println("sql:"+sql);
      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getNode(String workorderframe_id, String workordernode_id) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from two_node \n" +
                  " where workorderframe_id='" + workorderframe_id + "' and  \n" +
                  " workordernode_id='" + workordernode_id + "'";

          List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }


  public List getFatherNode2(String workorderframe_id, WorkOrderActionForm form) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from two_node a, two_link b \n" +
                  " where b.workorderframe_id='" + workorderframe_id + "' and  \n" +
                  " b.fathernode_id=a.workordernode_id and b.sonnode_id='" + form.getCurStep_ID() + "'";

          List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }
  
  public List getDealSMS() throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select DocumentOut_GUID, DocumentPath, DocumentTo, DocumentFrom from documentout where (status=0 or (status=5 and TrialCount<TrialCountLimit)) and DocType=3 and ((ExpectedStartDate = '' or ExpectedStartDate is null) or (ExpectedStartDate<getdate()))";

          List resultList = null;
          System.out.println(sql);
      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getWPAllNodeInfo(String workorderframe_id) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "SELECT * FROM two_NODE WHERE WORKORDERFRAME_ID='"+workorderframe_id+"'";

          List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }



  public List getFatherNode(String workorderframe_id, WorkOrderActionForm form) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from two_node a, two_link b \n" +
                  " where b.workorderframe_id='" + workorderframe_id + "' and  \n" +
                  " b.sonnode_id=a.workordernode_id and b.sonnode_id='" + form.getCurStep_ID() + "'";

          List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }


  public List getBeginNode(String workorderframe_id, String FATHERNODE_ID) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select B.* from two_frame a, two_node b, two_link c \n" +

                  " where a.workorderframe_id='" + Util.filter(workorderframe_id) + "' \n" +
                  " and a.workorderframe_id=C.workorderframe_id and C.SONNODE_ID=B.WORKORDERNODE_ID AND \n" +
                  " c.FATHERNODE_ID='"+FATHERNODE_ID+"'";

      List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }



  public List getNextStep(String workorderframe_id, String CurStep_ID) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

	  String sql = "select * from two_node a, two_link b \n" +
		  "where b.workorderframe_id='"+workorderframe_id+"' and  \n" +
		  "b.sonnode_id=a.workordernode_id and b.fathernode_id='"+CurStep_ID+"'";

	  List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public int getSourceListCount(cn.com.elite.workorder.WorkOrderActionForm model) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select 1 from EliteSource \n" +
                  "where 1=1  \n";
              if(!model.getSource_desc().equals("")){
                  sql += " and source_desc like '%"+model.getSource_desc()+"%' ";
              }
              if(!model.getSourceType1().equals("")){
                  sql += " and type1 = '"+model.getSourceType1()+"' ";
              }
              if(!model.getSourceType2().equals("")){
                  sql += " and type2 = '"+model.getSourceType2()+"' ";
              }




          List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }

      if(resultList == null){
          return 0;
      }

      return resultList.size();
  }

  public int getReportListCount(cn.com.elite.workorder.WorkOrderActionForm model) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select 1 from EliteReport \n" +
                  "where 1=1  \n";
              if(!model.getProjectName().equals("")){
                  sql += " and projectName like '%"+model.getProjectName()+"%' ";
              }
              if(!model.getReport_desc().equals("")){
                  sql += " and report_desc like '%"+model.getReport_desc()+"%' ";
              }



          List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }

      if(resultList == null){
          return 0;
      }

      return resultList.size();
  }


  public List getSourceList(cn.com.elite.workorder.WorkOrderActionForm model, int startRow,
	  int intPageSize) throws Exception {

	  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

//        String sql = "select * from sfsj_product1 a, sfsj_product b, sfsj_product_picture c, sfsjc_colour d "+
//            " where a.product_id1 = b.product_id1  and a.product_id1 = c.product_id1 and c.colour=d.colour_id  ";
	  String sql = "select d.type2_desc, b.staffname, c.type1_desc, a.* from EliteSource a\n" +
	  " left join EliteSourceType2 d on a.type2=d.type2_id \n"+
	  " , staff b, EliteSourceType1 c  \n"+
		  "where a.staff_id=b.staff_id and a.type1=c.type1_id  \n";
	  if (!model.getSource_desc().equals("")) {
		  sql += " and a.source_desc like '%" + model.getSource_desc() + "%' ";
	  }
	  if (!model.getSourceType1().equals("")) {
		  sql += " and a.type1 = '" + model.getSourceType1() + "' ";
	  }
      if(!model.getSourceType2().equals("")){
                  sql += " and type2 = '"+model.getSourceType2()+"' ";
              }

	  sql += " order by substring(source_desc, 1, 6) ";

	  System.out.println("sql:" + sql);
	  List productList = null;
	  List returnproductList = new ArrayList();
	  try {
		  productList = jdbcTemplate.queryForList(sql);
		  //int index = 1;
		  String up_product_id1 = "";
		  for (int i = 0; i < productList.size(); i++) {
			  if (i + 1 < startRow) {
				  continue;
			  }
			  if (i < startRow + intPageSize - 1) {
				  returnproductList.add(productList.get(i));
			  }
//
//                if ( i+1 < startRow) {
//                    continue;
//                }
//
//                if(i < intPageSize) {
//                    returnproductList.add(productList.get(i));
//                }

		  }

	  }
	  catch (Exception ex) {
		  ex.printStackTrace();
	  }

	  return returnproductList;
  }

  public int getBugListCount(cn.com.elite.workorder.WorkOrderActionForm model, String S_STAFF_ID) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

      String sql = "select b.staffname, a.* from BugList a, staff b \n" +
              "where a.startstaff=b.staff_id   \n";
      if (!model.getOb_staff().equals("")) {
              sql += " and a.ob_staff = '" + model.getOb_staff() + "' ";
      }
      if (!model.getStartCustomer().equals("")) {
              sql += " and startstaff = '" + model.getStartCustomer() + "' ";
      }

      if (!model.getBug_type().equals("")) {
              sql += " and a.bug_type like '%" + model.getBug_type() + "%' ";
      }
      if (!model.getBug_status().equals("")) {
              sql += " and a.bug_status = '" + model.getBug_status() + "' ";
      }

      if (!model.getSelf_deal().equals("") && !model.getSelf_deal().equals("2")) {
              sql += "  and ((bug_status = 'OPEN' and ob_staff='" + S_STAFF_ID +
                      "') or (bug_status = 'CHECK' and startstaff='" + S_STAFF_ID + "')) ";
      }
      
      if (model.getIsproposal().equals("")) {
          sql += " and (a.isproposal <> '1' or a.isproposal is null) ";
      }	else {
    	  sql += " and a.isproposal = '1' ";
      }

      System.out.println(sql);

          List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }

      if(resultList == null){
          return 0;
      }

      return resultList.size();
  }

  public List getReportList(cn.com.elite.workorder.WorkOrderActionForm model, int startRow,
            int intPageSize) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

//        String sql = "select * from EliteReport sfsj_product1 a, sfsj_product b, sfsj_product_picture c, sfsjc_colour d "+
//            " where a.product_id1 = b.product_id1  and a.product_id1 = c.product_id1 and c.colour=d.colour_id  ";
      String sql = "select b.staffname, a.* from EliteReport a, staff b \n" +
              "where a.staff_id=b.staff_id   \n";
          if(!model.getProjectName().equals("")){
                sql += " and a.projectName like '%"+model.getProjectName()+"%' ";
            }
            if(!model.getReport_desc().equals("")){
                sql += " and a.report_desc like '%"+model.getReport_desc()+"%' ";
            }

      sql += " order by a.createdate desc ";

      System.out.println("sql:" + sql);
      List productList = null;
      List returnproductList = new ArrayList();
      try {
              productList = jdbcTemplate.queryForList(sql);
              //int index = 1;
              String up_product_id1 = "";
              for (int i = 0; i < productList.size(); i++) {
                      if (i + 1 < startRow) {
                              continue;
                      }
                      if (i < startRow + intPageSize - 1) {
                              returnproductList.add(productList.get(i));
                      }
//
//                if ( i+1 < startRow) {
//                    continue;
//                }
//
//                if(i < intPageSize) {
//                    returnproductList.add(productList.get(i));
//                }

              }

      }
      catch (Exception ex) {
              ex.printStackTrace();
      }

            return returnproductList;
  }


  public List getBugList(cn.com.elite.workorder.WorkOrderActionForm model, int startRow,
            int intPageSize, String S_STAFF_ID) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

//        String sql = "select * from sfsj_product1 a, sfsj_product b, sfsj_product_picture c, sfsjc_colour d "+
//            " where a.product_id1 = b.product_id1  and a.product_id1 = c.product_id1 and c.colour=d.colour_id  ";
	  String sql = "select b.staffname, a.*, t2.Project_Name, t3.Project_Name sub_project_name from BugList a \n"+
	  " left join  ProjectInfo t2 on a.project_id=t2.Project_id \n"+
	  " left join  ProjectInfo t3 on a.sub_project_id=t3.Project_id \n"+
	  " , staff b \n" +
		  "where a.startstaff=b.staff_id   \n";
	  if (!model.getOb_staff().equals("")) {
		  sql += " and a.ob_staff = '" + model.getOb_staff() + "' ";
	  }
	  if (!model.getStartCustomer().equals("")) {
		  sql += " and startstaff = '" + model.getStartCustomer() + "' ";
	  }

	  if (!model.getBug_type().equals("")) {
		  sql += " and a.bug_type like '%" + model.getBug_type() + "%' ";
	  }
	  if (!model.getBug_status().equals("")) {
		  sql += " and a.bug_status = '" + model.getBug_status() + "' ";
	  }

	  if (!model.getSelf_deal().equals("") && !model.getSelf_deal().equals("2")) {
		  sql += "  and ((bug_status = 'OPEN' and ob_staff='" + S_STAFF_ID +
			  "') or (bug_status = 'CHECK' and startstaff='" + S_STAFF_ID + "')) ";
	  }
	  if (model.getIsproposal().equals("")) {
          sql += " and (a.isproposal <> '1' or a.isproposal is null) ";
      }	else {
    	  sql += " and a.isproposal = '1' ";
      }

        //replace(me.txtUserName.text,"'","''")

      sql += " order by a.starttime desc ";

      System.out.println("sql:" + sql);
      List productList = null;
      List returnproductList = new ArrayList();
      try {
              productList = jdbcTemplate.queryForList(sql);
              //int index = 1;
              String up_product_id1 = "";
              for (int i = 0; i < productList.size(); i++) {
                      if (i + 1 < startRow) {
                              continue;
                      }
                      if (i < startRow + intPageSize - 1) {
                              returnproductList.add(productList.get(i));
                      }
//
//                if ( i+1 < startRow) {
//                    continue;
//                }
//
//                if(i < intPageSize) {
//                    returnproductList.add(productList.get(i));
//                }

              }

      }
      catch (Exception ex) {
              ex.printStackTrace();
      }

            return returnproductList;
  }

  public int insertTABCBZWORKFLOW(WorkOrderActionForm form) throws Exception {

	  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

	  String sql = "Insert Into TABCBZWORKFLOW (Relation_ID ,Dept,Priority,level,FlowDetail,deadline,caseid,method,type) " +
		  " Values (newid(),'"+Util.filter(form.getWp_dept())+"','"+Util.filter(form.getWp_level())+"', "+
          "'"+Util.filter(form.getYanzhongchengdu())+"','"+Util.filter(form.getWp_desc())+"',"+
          " '"+Util.filter(form.getHopejiejuetime())+"','"+Util.filter(form.getWp_id())+"','"+Util.filter(form.getJiejuefangfa())+"','"+Util.filter(form.getWp_type())+"')";
	  System.out.println(sql);
	  int result = jdbcTemplate.update(sql);

	  return result;
  }

  public int insertTABCEliteWO(WorkOrderActionForm form) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "Insert Into TABCEliteWO (Relation_ID ,QuestionDesc,method,questiontype,prodtype1,prodtype2,commitdate,expectdate,valuable, ifpublic, pm, zhongyao, project_id, sub_project_id) " +
                  " Values ('"+Util.filter(form.getWp_id())+"','"+Util.filter(form.getQuestiondesc())+"','', "+
          "'"+Util.filter(form.getQuestionType())+"','"+Util.filter(form.getProdType1())+"',"+
          " '"+Util.filter(form.getProdType2())+"',getdate(),'"+Util.filter(form.getEnd_time())+"', 0, 0, '"+Util.filter(form.getPm())+"', '"+Util.filter(form.getZhongyao())+"', '"+Util.filter(form.getProject_id())+"', '"+Util.filter(form.getSub_Project_id())+"')";
          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }

  public int insertTABCEliteWOForElite(WorkOrderActionForm form) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "Insert Into TABCEliteWO (Relation_ID ,QuestionDesc,method,questiontype,prodtype1,prodtype2,commitdate,expectdate,valuable, ifpublic, pm, zhongyao, project_id, sub_project_id) " +
                  " Values ('"+Util.filter(form.getWp_id())+"','"+Util.filter(form.getQuestiondesc())+"','', "+
          "'"+Util.filter(form.getQuestionType())+"','"+Util.filter(form.getProdType1())+"',"+
          " '"+Util.filter(form.getProdType2())+"',getdate(),'"+Util.filter(form.getEnd_time())+"', 3, 0, '"+Util.filter(form.getPm())+"', '"+Util.filter(form.getZhongyao())+"', '"+Util.filter(form.getProject_id())+"', '"+Util.filter(form.getSub_Project_id())+"')";
          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }



  public int insertFile(String wp_id, String filename, String localfilename, String staffname) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "Insert Into t_workorder_filelist (attachefile_guid ,filename1,relation_id,localfilename,staffname,ftppath) " +
                  " Values (newid(),'"+Util.filter(localfilename)+"','"+Util.filter(wp_id)+"', "+
          "'"+Util.filter(filename)+"','"+Util.filter(staffname)+"',"+
          " '')";
          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }

  public int insertSourceFile(String wp_id, String filename, String localfilename, String staffname) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "Insert Into EliteSourceFile (attachefile_guid ,filename1,relation_id,localfilename,staffname) " +
                  " Values (newid(),'"+Util.filter(localfilename)+"','"+Util.filter(wp_id)+"', "+
          "'"+Util.filter(filename)+"','"+Util.filter(staffname)+"'"+
          " )";
          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }

  public int insertReportFile(String wp_id, String filename, String localfilename, String staffname) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "Insert Into EliteReportFile (attachefile_guid ,filename1,relation_id,localfilename,staffname) " +
                  " Values (newid(),'"+Util.filter(localfilename)+"','"+Util.filter(wp_id)+"', "+
          "'"+Util.filter(filename)+"','"+Util.filter(staffname)+"'"+
          " )";
          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }
  
  public int insertinto(String sql)  {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

      int result = 0;
      try {
		java.sql.Connection conn = dataSource.getConnection();
	} catch (SQLException e1) {
		// TODO Auto-generated catch block
		e1.printStackTrace();
	}
      System.out.println("sql="+sql);
      try{
	      result = jdbcTemplate.update(sql);
	      System.out.println("更新数量result="+result);
      } catch(Exception e){
    	  e.printStackTrace();
      }
      return result;
}

  public int insertBuglistFile(String wp_id, String filename, String localfilename, String staffname) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "Insert Into BuglistFile (attachefile_guid ,filename1,relation_id,localfilename,staffname) " +
                  " Values (newid(),'"+Util.filter(localfilename)+"','"+Util.filter(wp_id)+"', "+
          "'"+Util.filter(filename)+"','"+Util.filter(staffname)+"'"+
          " )";
          
          int result = jdbcTemplate.update(sql);
          
          return result;
  }
  
  public int insertProjectdoclistFile(String ProjectDoc_id, String ProjectTask, String filename, String localfilename, String staffname) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

      String sql = "Insert Into projectdoclistFile (attachefile_guid ,filename1,ProjectDoc_id,ProjectTask,localfilename,staffname) " +
              " Values (newid(),'"+Util.filter(localfilename)+"','"+Util.filter(ProjectDoc_id)+"','"+Util.filter(ProjectTask)+"', "+
      "'"+Util.filter(filename)+"','"+Util.filter(staffname)+"'"+
      " )";
      System.out.println(sql);
      int result = jdbcTemplate.update(sql);

      return result;
}

  public int insertAftersaleFile(String wp_id, String filename, String localfilename, String staffname) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "Insert Into EliteAftersaleFile (attachefile_guid ,filename1,relation_id,localfilename,staffname) " +
                  " Values (newid(),'"+Util.filter(localfilename)+"','"+Util.filter(wp_id)+"', "+
          "'"+Util.filter(filename)+"','"+Util.filter(staffname)+"'"+
          " )";
          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }
  
  public int delSource(String source_id) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

      String sql = "delete from EliteSource where source_id='"+source_id+"'";
      System.out.println(sql);
      int result = jdbcTemplate.update(sql);

      return result;
}



  public int delWPFile(String wp_id, String attachefile_guid) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "delete from  t_workorder_filelist where relation_id='"+Util.filter(wp_id)+"' and attachefile_guid='"+Util.filter(attachefile_guid)+"' ";
          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }

  public int delSourceFile(String wp_id, String attachefile_guid) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "delete from  EliteSourceFile where relation_id='"+Util.filter(wp_id)+"' and attachefile_guid='"+Util.filter(attachefile_guid)+"' ";
          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }

  public int delReportFile(String wp_id, String attachefile_guid) throws Exception {

            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "delete from  EliteReportFile where relation_id='"+Util.filter(wp_id)+"' and attachefile_guid='"+Util.filter(attachefile_guid)+"' ";
            System.out.println(sql);
            int result = jdbcTemplate.update(sql);

            return result;
  }

  public int delBuglistFile(String wp_id, String attachefile_guid) throws Exception {

            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "delete from  BuglistFile where relation_id='"+Util.filter(wp_id)+"' and attachefile_guid='"+Util.filter(attachefile_guid)+"' ";
            System.out.println(sql);
            int result = jdbcTemplate.update(sql);

            return result;
  }

  public int delAftersaleFile(String wp_id, String attachefile_guid) throws Exception {

            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "delete from  EliteAftersaleFile where relation_id='"+Util.filter(wp_id)+"' and attachefile_guid='"+Util.filter(attachefile_guid)+"' ";
            System.out.println(sql);
            int result = jdbcTemplate.update(sql);

            return result;
  }




  public int updateTABCBZWORKFLOW(WorkOrderActionForm form) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "update TABCEliteWO set \n"+
              " QuestionDesc='"+form.getQuestiondesc()+"', \n"+
              " method='"+form.getMethod()+"', \n"+
              " questiontype='"+form.getQuestionType()+"', \n"+
              " prodtype1='"+form.getProdType1()+"', \n"+
              " prodtype2='"+form.getProdType2()+"', \n"+
              " zhongyao='"+form.getZhongyao()+"', \n"+
              " project_id='"+form.getProject_id()+"', \n"+
              " sub_project_id='"+form.getSub_Project_id()+"', \n"+
              " ifpublic='"+form.getIfpublic()+"' \n" +
              " where Relation_ID='"+form.getWorkorder_guid()+"'";

//
//
//
//              (Relation_ID ,Dept,Priority,level,FlowDetail,deadline,caseid,method,type) " +
//                  " Values (newid(),'"+Util.filter(form.getWp_dept())+"','"+Util.filter(form.getWp_level())+"', "+
//          "'"+Util.filter(form.getYanzhongchengdu())+"','"+Util.filter(form.getWp_desc())+"',"+
//          " '"+Util.filter(form.getHopejiejuetime())+"','"+Util.filter(form.getWp_id())+"','"+Util.filter(form.getJiejuefangfa())+"','"+Util.filter(form.getWp_type())+"')";
          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }


  public int updateCustomer(WorkOrderActionForm form, String staff_id) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "UPDATE Customer SET CUSTOMERNAME='"+Util.filter(form.getCUSTOMERNAME())+"',STRINGFIELD5='"+Util.filter(form.getGongsimingcheng())+"' \n "+

              "  WHERE Customer_GUID='"+Util.filter(form.getCustomer_GUID())+"'";
          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }

  public String getNewGuid() throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select newid() as new_guid ";

          Map map = jdbcTemplate.queryForMap(sql);

          return Util.filterForObject(map.get("new_guid"));
  }

  public List getTopProjectName() throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

      String sql = "select * from ProjectInfo where father_project_id='0' order by project_name\n";

      List resultList = null;
      try {
          //jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.queryForList(sql);
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      return resultList;
  }
  
  public List getProjectName(String father_project_id) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

      String sql = "select * from ProjectInfo where father_project_id='" + father_project_id +
          "'  order by project_name\n";

      List resultList = null;
      try {
          //jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.queryForList(sql);
      }
      catch (Exception e) {
          e.printStackTrace();
      }
      return resultList;
  }


  public int updateWorkOrder2End(WorkOrderActionForm form, String staff_id) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "update WorkOrder2 set \n"+
              //" PRIORITY_ID='"+form.getYouxiandengji()+"', \n"+
              //" EXPIREDDATE='"+form.getWp_end_time()+"', \n"+
              " WorkOrderStatus='CLOSE', \n"+
              " ModifiedDate=getdate() \n"+
              //" ExpectStartTime='"+form.getNextstarttime()+"' \n"+
              " where WORKORDER_GUID = '"+form.getWorkorder_guid()+"'";




//
//
//              sql = "Insert into WorkOrder2 (WORKORDER_GUID, WORKORDERFRAME_ID,CATEGORY,PRIORITY_ID,EXPIREDDATE,CUSTOMER_GUID, " +
//                  "SUBSCRIBER_GUID,ACCOUNT_GUID,WORKORDERSTATUS,CREATEDBY,CREATEDDATE,MODIFIEDBY, MODIFIEDDATE,ExpectStartTime, " +
//                  "CurStepStatus,CurStep_ID,HandleGroup_ID,HandleBy_ID,  ProcessFlag,StepExpiredDate,CurStepComments, " +
//                  "ExpiredHandleGroup_ID,ExpiredHandleBy_ID, StringField1,StringField2,StringField3,StringField4,StringField5) values  " +
//                  "('" + newguid + "','" + form.getWORKORDERFRAME_ID() + "','WORDER','" +
//                  form.getYouxiandengji() + "','" + form.getWp_end_time() + "', " +
//                  "'" + Util.filter(form.getCustomer_GUID()) + "','','','OPEN','" + Util.filter(staff_id) +
//                  "', " +
//                  "getdate(),'" + Util.filter(staff_id) + "','getdate()','" + form.getNextstarttime() +
//                  "','OPEN','" + form.getWORKORDERNODE_ID2() + "','" + form.getHANDLEGROUP_ID() + "', " +
//                  "'" + form.getHANDLEBY_ID() + "',1,'" + form.getWp_end_time() +
//                  "','','','','','','','','')";
          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }


  public int updateWorkOrder2Sends(WorkOrderActionForm form, String staff_id) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "update WorkOrder2 set \n"+
              " PRIORITY_ID='"+form.getYouxiandengji()+"', \n"+
              " ModifiedBy='"+staff_id+"', \n"+
              " ModifiedDate=getdate(), \n"+
//              " EXPIREDDATE=EXPIREDDATE+1, \n"+
              " ExpectStartTime=getdate(), \n"+
              " CurStep_ID='"+form.getWORKORDERNODE_ID()+"', \n"+
              " HandleGroup_ID='"+form.getHANDLEGROUP_ID()+"', \n"+
              " HandleBy_ID='"+form.getHANDLEBY_ID()+"', \n"+
              " ExpiredHandleGroup_ID='"+form.getEXPIREDHANDLEGROUP_ID()+"', \n"+
              " ExpiredHandleBy_ID='"+form.getEXPIREDHANDLEBY_ID()+"' \n"+
              " where WORKORDER_GUID = '"+form.getWorkorder_guid()+"'";


          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }

  public int updateWorkOrder2Sends_aftersale(WorkOrderActionForm form, String staff_id) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "update WorkOrder2 set \n"+
//              " PRIORITY_ID='"+form.getYouxiandengji()+"', \n"+
              " ModifiedBy='"+staff_id+"', \n"+
              " ModifiedDate=getdate(), \n"+
//              " EXPIREDDATE=EXPIREDDATE+1, \n"+
//              " ExpectStartTime=getdate(), \n"+
              " CurStep_ID='"+form.getWORKORDERNODE_ID()+"', \n"+
              " HandleGroup_ID='"+form.getHANDLEGROUP_ID()+"', \n"+
              " HandleBy_ID='"+form.getHANDLEBY_ID()+"' \n"+
//              " ExpiredHandleGroup_ID='"+form.getEXPIREDHANDLEGROUP_ID()+"', \n"+
//              " ExpiredHandleBy_ID='"+form.getEXPIREDHANDLEBY_ID()+"' \n"+
              " where WORKORDER_GUID = '"+form.getWorkorder_guid()+"'";


          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }

  public int updateWorkOrder2Sends_aftersale2(WorkOrderActionForm form, String staff_id) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "update WorkOrder2 set \n"+
//              " PRIORITY_ID='"+form.getYouxiandengji()+"', \n"+
              " WORKORDERSTATUS = 'CLOSE' where WORKORDER_GUID = '"+form.getWorkorder_guid()+"'";


          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }



  public int updateWorkOrder2(WorkOrderActionForm form) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "update WorkOrder2 set \n"+

              " HandleGroup_ID='"+form.getPm_group()+"', \n"+
              " HandleBy_ID='"+form.getPm()+"' \n"+

              " where WORKORDER_GUID = '"+form.getWorkorder_guid()+"'";


          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }
  
  public int updateWorkOrder2FAIL(WorkOrderActionForm form) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

      String sql = "update WorkOrder2 set \n"+

          //" HandleGroup_ID='"+form.getPm_group()+"', \n"+
          " workorderstatus='FAIL' \n"+

          " where WORKORDER_GUID = '"+form.getWorkorder_guid()+"'";


      System.out.println(sql);
      int result = jdbcTemplate.update(sql);

      return result;
}


  public int updateWorkOrder2TAKE(WorkOrderActionForm form, String staff_id,  String S_GROUP_ID) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "update WorkOrder2 set \n"+
              " PRIORITY_ID='"+form.getYouxiandengji()+"', \n"+
              " EXPIREDDATE=getdate()+1, \n"+
              //" ExpectStartTime='"+form.getNextstarttime()+"', \n"+
              " ModifiedBy='"+form.getHANDLEBY_ID()+"', \n"+
              " ModifiedDate=getdate(), \n"+
              " HandleGroup_ID='"+S_GROUP_ID+"', \n"+
              " HandleBy_ID='"+staff_id+"' \n"+

              " where WORKORDER_GUID = '"+form.getWorkorder_guid()+"'";


          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }


  public int updateWorkOrder2BACK(WorkOrderActionForm form, String staff_id) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "update WorkOrder2 set \n"+
              " PRIORITY_ID='"+form.getYouxiandengji()+"', \n"+
              " EXPIREDDATE='"+form.getWp_end_time()+"', \n"+
              " ModifiedDate=getdate(), \n"+
              " ExpectStartTime='"+form.getNextstarttime()+"', \n"+
              " CurStep_ID='"+form.getWORKORDERNODE_ID()+"', \n"+
              " HandleGroup_ID='"+form.getHANDLEGROUP_ID()+"', \n"+
              " HandleBy_ID='"+form.getHANDLEBY_ID()+"', \n"+
              " ExpiredHandleGroup_ID='"+form.getEXPIREDHANDLEGROUP_ID()+"', \n"+
              " ExpiredHandleBy_ID='"+form.getEXPIREDHANDLEBY_ID()+"' \n"+
              " where WORKORDER_GUID = '"+form.getWorkorder_guid()+"'";


          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }


  public int updateWorkOrder2FAILED(WorkOrderActionForm form, String staff_id) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "update WorkOrder2 set \n"+
              " PRIORITY_ID='"+form.getYouxiandengji()+"', \n"+
              " EXPIREDDATE='"+form.getWp_end_time()+"', \n"+
              " ModifiedDate=getdate(), \n"+
              " ExpectStartTime='"+form.getNextstarttime()+"', \n"+
              " WorkOrderStatus='FAILED' \n"+

              " where WORKORDER_GUID = '"+form.getWorkorder_guid()+"'";


          System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }


  public int deleteWORKORDERPATH2(WorkOrderActionForm form) throws Exception {

	  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

	  String sql = "select top 1 workorderpath_guid from WORKORDERPATH2  where workorder_guid='" + form.getWorkorder_guid() +
		  "' order by modifieddate desc";

	  List resultList = null;

	  try {
		  //jdbcTemplate.queryForList(sql);
		  resultList = jdbcTemplate.queryForList(sql);
	  }
	  catch (Exception e) {
		  e.printStackTrace();
	  }

      String workorderpath_guid = "";
	  if (resultList != null && resultList.size() > 0) {

		  Map map = (Map) resultList.get(0);
		  workorderpath_guid = cn.com.elite.util.Util.filterForObject(map.get("workorderpath_guid"));

	  }

      sql = "delete from workorderpath2 where WorkOrderPath_Guid='" + workorderpath_guid + "'";
            System.out.println(sql);
            int result = jdbcTemplate.update(sql);

	  return result;
}
  public String getPmGroupID(String pm){


        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        String sql = "select a.ROLEGROUP_ID, A.ROLEGROUPDESCRIPTION, B.staff_id, B.AGENT_ID, B.password, B.staffname   from rolegroup a, staff b, staffrolegroup c \n" +
                    "where a.ROLEGROUP_ID = c.ROLEGROUP_ID and  \n" +
                    "b.STAFF_ID = c.STAFF_ID  and Status=1 \n" +

                    "and b.staff_id='" + Util.filter(pm) + "' \n";

        System.out.println("sql:"+sql);
        Map map = null;
        try {
          map = jdbcTemplate.queryForMap(sql);
        }
        catch (Exception ex) {
          return "";
        }


        if(map == null){

            return "";
        }
        return Util.filterForObject(map.get("ROLEGROUP_ID"));

    }

  public int insertWORKORDERSTEP2(WorkOrderActionForm form, String staff_id, String STEP2newguid, String status) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);


		  String sql = "Insert Into WORKORDERSTEP2(WORKORDER_GUID,WORKORDERSTEP_GUID,OldHANDLEBY_ID, "+
                          "OldHANDLEGROUP_ID,HANDLEBY_ID,HANDLEGROUP_ID, \n" +
			  "WORKORDERSTEPSTATUS,COMMENTS,ModifiedDate,WORKORDERSTEPID,StepExpired)  \n" +
			  "Values ('" + form.getWorkorder_guid() + "','" + STEP2newguid + "', \n" +
			  "'" + form.getCreatebyid() + "','" + form.getHANDLEGROUP_ID2() + "','" +
			  form.getHANDLEBY_ID() + "','" + form.getHANDLEGROUP_ID() + "','" + status + "','" +
			  form.getWp_deal_desc() + "',getdate(),'" + form.getWORKORDERNODE_ID2() + "',getdate() )";
		  System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }

  public int insertWORKORDERSTEP2_aftersale(WorkOrderActionForm form, String staff_id, String STEP2newguid, String status) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);


                  String sql = "Insert Into WORKORDERSTEP2(WORKORDER_GUID,WORKORDERSTEP_GUID,OldHANDLEBY_ID, "+
                          "OldHANDLEGROUP_ID,HANDLEBY_ID,HANDLEGROUP_ID, \n" +
                          "WORKORDERSTEPSTATUS,COMMENTS,ModifiedDate,WORKORDERSTEPID,StepExpired)  \n" +
                          "Values ('" + form.getWorkorder_guid() + "','" + STEP2newguid + "', \n" +
                          "'" + form.getCreatebyid() + "','" + form.getHANDLEGROUP_ID2() + "','" +
                          form.getHANDLEBY_ID() + "','" + form.getHANDLEGROUP_ID() + "','" + status + "','" +
                          form.getWp_deal_desc() + "',getdate(),'" + form.getWORKORDERNODE_ID2() + "',getdate() )";
                  System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }


  public int insertWORKORDERSTEP2(WorkOrderActionForm form, String STEP2newguid, String status) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);


                  String sql = "Insert Into WORKORDERSTEP2(WORKORDER_GUID,WORKORDERSTEP_GUID,OldHANDLEBY_ID, "+
                          "OldHANDLEGROUP_ID,HANDLEBY_ID,HANDLEGROUP_ID, \n" +
                          "WORKORDERSTEPSTATUS,COMMENTS,ModifiedDate,WORKORDERSTEPID,StepExpired)  \n" +
                          "Values ('" + form.getWorkorder_guid() + "','" + STEP2newguid + "', \n" +
                          "'" + form.getCreatebyid() + "','" + form.getHANDLEGROUP_ID2() + "','" +
                          form.getHANDLEBY_ID() + "','" + form.getHANDLEGROUP_ID() + "','" + status + "','" +
                          form.getWp_deal_desc() + "',getdate(),'" + form.getWORKORDERNODE_ID() + "',getdate() )";
                  System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }


  public int updateWORKORDERSTEP2(WorkOrderActionForm form, String staff_id, String STEP2newguid, String status, String S_GROUP_ID) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);


          String sql = "update WORKORDERSTEP2 set HANDLEGROUP_ID='"+S_GROUP_ID+"', HANDLEBY_ID='"+staff_id+"', COMMENTS='" +
			  form.getWp_deal_desc() + "' "+
                      " where WORKORDER_GUID='"+form.getWorkorder_guid()+"'";

                  System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }


  public int insertObjective(WorkOrderActionForm form) throws Exception {

          JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);


                  String sql = "Insert Into OBJECTIVE(OBJECTIVE_GUID,CREATEDDATE,MODIFIEDDATE)  \n" +
                          "Values ('" + form.getWorkorder_guid() + "',getdate(), \n" +
                          "getdate() )";
                  System.out.println(sql);
          int result = jdbcTemplate.update(sql);

          return result;
  }

  public List search2ByCustomer(WorkOrderActionForm form, String S_STAFF_ID, String S_GROUP_ID) throws Exception {


            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

			String sql = "SELECT DISTINCT WorkOrder2.WorkOrder_GUID ,WorkOrder2.WorkOrderFrame_ID, \n" +
				"WorkOrder2.CURSTEP_ID,Customer.CustomerName,WorkOrder2.ExpectStartTime, \n" +
				"WorkOrder2.StepExpiredDate,WorkOrder2.CurStepComments,WorkOrder2.Priority_ID, \n" +
				"WorkOrder2.HandleGroup_ID,WorkOrder2.HandleBy_ID,WorkOrder2.ModifiedBy, \n" +
				"WorkOrder2.ModifiedDate,OBJECTIVE.COMMENTS,WorkOrder2.WORKORDERSTATUS, \n" +
				"WorkOrder2.ExpiredDate,WorkOrder2.Customer_GUID,WorkOrder2.processFlag, TABCEliteWO.*  \n" +
				"FROM WORKORDER2 , OBJECTIVE ,Customer,WORKORDERSTEP2 , TABCEliteWO  \n" +
				"WHERE  WorkOrder2.WORKORDER_GUID=TABCEliteWO.Relation_ID and --TABCEliteWO.valuable='0' and  \n" +
				" WorkOrder2.WORKORDERSTATUS='CLOSE' AND  \n" +
				"--WorkOrder2.HandleBy_ID ='SELITE' AND WorkOrder2.HandleGroup_ID ='SYSTEM'    AND  \n" +
				"WORKORDERSTEP2.WORKORDER_GUID = WorkOrder2.WORKORDER_GUID AND  \n" +
				"--(WORKORDERSTEP2.HANDLEGROUP_ID = 'SYSTEM' OR WORKORDERSTEP2.OldHANDLEGROUP_ID = 'SYSTEM')  AND  \n" +
				"Customer.CUSTOMER_GUID = WorkOrder2.CUSTOMER_GUID and   \n" +
				"OBJECTIVE.OBJECTIVE_GUID=WorkOrder2.WorkOrder_GUID and WorkOrder2.WORKORDERFRAME_ID='55B829'   \n" ;

            if(!form.getQuestionType().equals("")){
                sql += " and TABCEliteWO.questiontype='"+form.getQuestionType()+"' ";
            }
            if(!form.getProdType1().equals("")){
                sql += " and TABCEliteWO.prodtype1='"+form.getProdType1()+"' ";
            }
            if(!form.getProdType2().equals("")){
                sql += " and TABCEliteWO.prodtype2='"+form.getProdType2()+"' ";
            }

            if(!form.getQuestion_desc().equals("")){
                sql += " and TABCEliteWO.questiondesc like '%"+form.getQuestion_desc()+"%' ";
            }

            if(!form.getStartCustomer().equals("")){
                    sql += "   AND Customer.stringfield5 = '"+form.getStartCustomer()+"'  ";
                }

            sql += " and (TABCEliteWO.ifpublic>0 or WorkOrder2.customer_guid='"+S_STAFF_ID+"') ";
				sql += "order by WorkOrder2.EXPIREDDATE ";

            List resultList = null;
            System.out.println("sql:"+sql);
            try{
//              jdbcTemplate.queryForList(sql);
              resultList = jdbcTemplate.queryForList(sql);
            } catch (Exception e){
              e.printStackTrace();
            }


            return resultList;
  }

  public List search2(WorkOrderActionForm form, String S_STAFF_ID, String S_GROUP_ID) throws Exception {


            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

                        String sql = "SELECT DISTINCT WorkOrder2.WorkOrder_GUID ,WorkOrder2.WorkOrderFrame_ID, \n" +
                                "WorkOrder2.CURSTEP_ID,Customer.CustomerName,WorkOrder2.ExpectStartTime, \n" +
                                "WorkOrder2.StepExpiredDate,WorkOrder2.CurStepComments,WorkOrder2.Priority_ID, \n" +
                                "WorkOrder2.HandleGroup_ID,WorkOrder2.HandleBy_ID,WorkOrder2.ModifiedBy, \n" +
                                "WorkOrder2.ModifiedDate,OBJECTIVE.COMMENTS,WorkOrder2.WORKORDERSTATUS, \n" +
                                "WorkOrder2.ExpiredDate,WorkOrder2.Customer_GUID,WorkOrder2.processFlag, TABCEliteWO.*  \n" +
                                "FROM WORKORDER2 , OBJECTIVE ,Customer,WORKORDERSTEP2 , TABCEliteWO  \n" +
                                "WHERE  WorkOrder2.WORKORDER_GUID=TABCEliteWO.Relation_ID and --TABCEliteWO.valuable='1' and  \n" +
                                " WorkOrder2.WORKORDERSTATUS='CLOSE' AND  \n" +
                                "--WorkOrder2.HandleBy_ID ='SELITE' AND WorkOrder2.HandleGroup_ID ='SYSTEM'    AND  \n" +
                                "WORKORDERSTEP2.WORKORDER_GUID = WorkOrder2.WORKORDER_GUID AND  \n" +
                                "--(WORKORDERSTEP2.HANDLEGROUP_ID = 'SYSTEM' OR WORKORDERSTEP2.OldHANDLEGROUP_ID = 'SYSTEM')  AND  \n" +
                                "Customer.CUSTOMER_GUID = WorkOrder2.CUSTOMER_GUID and   \n" +
                                "OBJECTIVE.OBJECTIVE_GUID=WorkOrder2.WorkOrder_GUID and WorkOrder2.WORKORDERFRAME_ID='55B829'   \n" ;
                            if(!form.getQuestionType().equals("")){
                sql += " and TABCEliteWO.questiontype='"+form.getQuestionType()+"' ";
            }
            if(!form.getProdType1().equals("")){
                sql += " and TABCEliteWO.prodtype1='"+form.getProdType1()+"' ";
            }
            if(!form.getProdType2().equals("")){
                sql += " and TABCEliteWO.prodtype2='"+form.getProdType2()+"' ";
            }
            if(!form.getStartCustomer().equals("")){
                    sql += "   AND Customer.stringfield5 = '"+form.getStartCustomer()+"'  ";
                }

            if(!form.getQuestion_desc().equals("")){
                sql += " and TABCEliteWO.questiondesc like '%"+form.getQuestion_desc()+"%' ";
            }



                               sql += " order by WorkOrder2.EXPIREDDATE ";

            List resultList = null;
System.out.println("sql:"+sql);
            try{
//              jdbcTemplate.queryForList(sql);
              resultList = jdbcTemplate.queryForList(sql);
            } catch (Exception e){
              e.printStackTrace();
            }


            return resultList;
  }




  public String getRes(String tableName,String col1Name,String col2Name,String col1Value){


      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

      String sql = "select "+col2Name+" as va from "+tableName+" where "+col1Name+"='"+col1Value+"' ";

      System.out.println("sql:"+sql);
      Map map = null;
      try {
      	map = jdbcTemplate.queryForMap(sql);
      }
      catch (Exception ex) {
      	return "";
      }


      if(map == null){

          return "";
      }
      return Util.filterForObject(map.get("va"));

    }

    public String getStepOldHandleByID(String WORKORDER_GUID,String WORKORDERSTEPID){


      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

      String sql = "select OldHANDLEBY_ID from WORKORDERSTEP2 where WORKORDER_GUID='"+WORKORDER_GUID+"' and  WORKORDERSTEPID='"+WORKORDERSTEPID+"' ";

      System.out.println("sql:"+sql);
      Map map = null;
      try {
        map = jdbcTemplate.queryForMap(sql);
      }
      catch (Exception ex) {
        return "";
      }


      if(map == null){

          return "";
      }
      return Util.filterForObject(map.get("OldHANDLEBY_ID"));

    }

    public int updateTABCEliteWO(String id, String status) throws Exception {

              JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

              String sql = "update TABCEliteWO set valuable='"+status+"' where Relation_ID='"+id+"'";
              System.out.println(sql);
              int result = jdbcTemplate.update(sql);

              return result;
  }

  public int updateTABCEliteWO(String id) throws Exception {

              JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

              String sql = "update TABCEliteWO set valuable='1' where Relation_ID='"+id+"' and valuable = 0.0";
              System.out.println(sql);
              int result = jdbcTemplate.update(sql);

              return result;
  }


  public List getTABCBZWORKFLOWByWorkorder_guid(String workorder_guid) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from TABCEliteWO where relation_id='"+workorder_guid+"'";

      List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getEliteAftersaleByWorkorder_guid(String workorder_guid) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from EliteAftersale where aftersale_guid='"+workorder_guid+"'";

      List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }


  public List getEliteProdType1() throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from eliteprodtype1";

      List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getSourceType1() throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from EliteSourceType1";

      List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getSource(String id) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select a.source_id, b.staffname, a.* from EliteSource a, staff b where a.staff_id=b.staff_id and a.source_id = '"+id+"'";

      List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getReport(String id) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select b.staffname, a.* from EliteReport a, staff b where a.staff_id=b.staff_id and a.Report_id = '"+id+"'";

      List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getBug(String id) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select b.staffname, a.*, t2.Project_Name, t3.Project_Name sub_project_name from BugList a \n"+
          "left join  ProjectInfo t2 on a.project_id=t2.Project_id \n"+
          "left join  ProjectInfo t3 on a.sub_project_id=t3.Project_id  \n"+
          ", staff b where a.startstaff=b.staff_id and a.buglist_guid = '"+id+"'";

      List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }




  public List getSourceType2() throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from EliteSourceType2";

      List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }


  public List getSourceType2(String type1) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from EliteSourceType2 where type1_id='"+type1+"'";

      List resultList = null;
System.out.println(sql);
      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }




  public List getEliteProdType2() throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from eliteprodtype2";

      List resultList = null;

      try{
        //jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getEliteProdType2(String type1id) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from eliteprodtype2 where type1id='"+type1id+"'";

      List resultList = null;

      try{
//        jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }


  public List getEliteQuestionType() throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from elitequestiontype";

      List resultList = null;

      try{
//        jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }
  
  public List getProjectInfo(String Project_id, String father_Project_id) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

      String sql = "select * from ProjectInfo where 1=1  \n";

      if(!Project_id.equals("")){
    	  sql += " and Project_id='"+Project_id+"' ";
      }
      
      if(!father_Project_id.equals("")){
    	  sql += " and father_Project_id='"+father_Project_id+"'";
      }
      
      sql += " order by Project_name ";
      List resultList = null;
      try {
              //jdbcTemplate.queryForList(sql);
              resultList = jdbcTemplate.queryForList(sql);
      }
      catch (Exception e) {
              e.printStackTrace();
      }
      return resultList;
}

  public List getWPFile(String wp_id) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "select * from t_workorder_filelist where relation_id='"+wp_id+"'";

        List resultList = null;

        try{
//          jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.queryForList(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }

  public List getSourceFile(String wp_id) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "select * from EliteSourceFile where relation_id='"+wp_id+"'";

        List resultList = null;

        try{
//          jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.queryForList(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }

//  public List getReportFile(String wp_id) throws Exception {
//
//        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
//
//            String sql = "select * from EliteReportFile where relation_id='"+wp_id+"'";
//
//        List resultList = null;
//
//        try{
//          jdbcTemplate.queryForList(sql);
//          resultList = jdbcTemplate.queryForList(sql);
//        } catch (Exception e){
//          e.printStackTrace();
//        }
//
//
//        return resultList;
//  }


  public List getReportFile(String wp_id) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "select * from EliteReportFile where relation_id='"+wp_id+"'";

        List resultList = null;

        try{
//          jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.queryForList(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }

  public List getBuglistFile(String wp_id) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "select * from BuglistFile where relation_id='"+wp_id+"'";

        List resultList = null;

        try{
//          jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.queryForList(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }

  public List getAftersaleFile(String wp_id) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "select * from EliteAftersaleFile where relation_id='"+wp_id+"'";

        List resultList = null;

        try{
//          jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.queryForList(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }



  public int checkCustomerPwd(String CUSTOMER_GUID, String pwd) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "select count(1)   from customer where Stringfield10='"+pwd+"' and CUSTOMER_GUID='"+CUSTOMER_GUID+"'";

        int resultList = 0;

        try{
//          jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.queryForInt(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }

  public int updateCustomerPwd(String CUSTOMER_GUID, String pwd) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "update customer set Stringfield10='"+pwd+"' where CUSTOMER_GUID='"+CUSTOMER_GUID+"'";

        int resultList = 0;

        try{
          //jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.update(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }

  public int checkStaffPwd(String S_STAFF_ID, String pwd) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "select count(1)   from staff where STAFF_ID='"+S_STAFF_ID+"'  ";

            if(pwd.equals("")){
                sql += " and (password='"+pwd+"' or password is null)";
            } else {
                sql += " and password='"+pwd+"' ";
            }

        int resultList = 0;

        try{
//          jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.queryForInt(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }

  public int updateStaffPwd(String S_STAFF_ID, String pwd) throws Exception {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

            String sql = "update staff set password='"+pwd+"' where STAFF_ID='"+S_STAFF_ID+"'";

        int resultList = 0;

        try{
          //jdbcTemplate.queryForList(sql);
          resultList = jdbcTemplate.update(sql);
        } catch (Exception e){
          e.printStackTrace();
        }


        return resultList;
  }



  public List getWorkOrderStepList(String workorder_guid) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

	  String sql = "SELECT b.workordernodename, c.WORKORDERSTEPSTATUSDESC, a.* \n" +
		  "FROM WORKORDERSTEP2 a, two_node b, SYSC_WORKORDERSTEPSTATUS c \n" +
		  "where a.workorder_guid='"+workorder_guid+"' \n" +
		  "and a.workorderstepid=b.workordernode_id \n" +
		  "and a.workorderstepstatus=c.WORKORDERSTEPSTATUS_ID \n" +
		  "order by modifieddate desc ";

      List resultList = null;

      try{
//        jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getCustomerList() throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select DISTINCT stringfield5   from customer where Stringfield12='1' and stringfield5 is not null ";

      List resultList = null;

      try{
//        jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }



  public List getWorkorder2ByWorkorder_guid(String workorder_guid) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from workorder2 where workorder_guid='"+workorder_guid+"'";

      List resultList = null;

      try{
//        jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getWORKORDERPATH2ByWorkorder_guid(String workorder_guid) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from WORKORDERPATH2 where workorder_guid='"+workorder_guid+"'";

      List resultList = null;

      try{
//        jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getWORKORDERSTEP2ByWorkorder_guid(String workorder_guid) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from WORKORDERSTEP2 where workorder_guid='"+workorder_guid+"'  order by modifieddate";

      List resultList = null;

      try{
//        jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getROLEGROUP() throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from ROLEGROUP";

      List resultList = null;

      try{
//        jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getStaff() throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from staff order by staffname";

      List resultList = null;

      try{
//        jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getAftersaleType() throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from EliteAftersaleType";

      List resultList = null;

      try{
//        jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }

  public List getAftersaleObject() throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from EliteAftersaleObject";

      List resultList = null;

      try{
//        jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }


  public List getStaff(String group_id) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from STAFFROLEGROUP, staff where STAFFROLEGROUP.staff_id=staff.staff_id and rolegroup_id='"+group_id+"'";

      List resultList = null;

      try{
//        jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }




  public List getCustomerByCustomer_guid(String Customer_guid) throws Exception {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

          String sql = "select * from Customer where Customer_guid='"+Customer_guid+"'";

      List resultList = null;

      try{
//        jdbcTemplate.queryForList(sql);
        resultList = jdbcTemplate.queryForList(sql);
      } catch (Exception e){
        e.printStackTrace();
      }


      return resultList;
  }




}
